package zy.dao.vip.rate.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.rate.MemberRateDAO;
import zy.entity.vip.rate.T_Vip_Brand_Rate;
import zy.entity.vip.rate.T_Vip_Type_Rate;

@Repository
public class MemberRateDAOImpl extends BaseDaoImpl implements MemberRateDAO{

	@Override
	public Integer count(Map<String, Object> param) {
		Object mt_code = param.get("mt_code");
		Object br_bd_code = param.get("br_bd_code");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT count(1) ");
		sql.append(" FROM t_vip_brand_rate t");
		sql.append(" JOIN t_base_brand b");
		sql.append(" ON b.bd_code=t.br_bd_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.br_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		if(null != mt_code && !"".equals(mt_code)){
			sql.append(" AND br_mt_code=:mt_code");
		}
		if(null != br_bd_code && !"".equals(br_bd_code)){
			sql.append(" AND br_bd_code=:br_bd_code");
		}
		sql.append(" AND t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Brand_Rate> list(Map<String, Object> param) {
		Object mt_code = param.get("mt_code");
		Object br_bd_code = param.get("br_bd_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT br_id,br_bd_code,bd_name");
		sql.append(" ,br_mt_code,mt_name,br_rate");
		sql.append(" FROM t_vip_brand_rate t");
		sql.append(" JOIN t_base_brand b");
		sql.append(" ON b.bd_code=t.br_bd_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.br_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		if(null != mt_code && !"".equals(mt_code)){
			sql.append(" AND br_mt_code=:mt_code");
		}
		if(null != br_bd_code && !"".equals(br_bd_code)){
			sql.append(" AND br_bd_code=:br_bd_code");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Vip_Brand_Rate.class));
	}

	@Override
	public Integer countType(Map<String, Object> param) {
		Object mt_code = param.get("mt_code");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT count(1) ");
		sql.append(" FROM t_vip_type_rate t");
		sql.append(" JOIN t_base_type b");
		sql.append(" ON b.tp_code=t.tr_tp_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.tr_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		if(null != mt_code && !"".equals(mt_code)){
			sql.append(" AND tr_mt_code=:mt_code");
		}
		sql.append(" AND t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Type_Rate> listType(Map<String, Object> param) {
		Object mt_code = param.get("mt_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT tr_id,tr_tp_code,tp_name");
		sql.append(" ,tr_mt_code,mt_name,tr_rate");
		sql.append(" FROM t_vip_type_rate t");
		sql.append(" JOIN t_base_type b");
		sql.append(" ON b.tp_code=t.tr_tp_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.tr_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		if(null != mt_code && !"".equals(mt_code)){
			sql.append(" AND tr_mt_code=:mt_code");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Vip_Type_Rate.class));
	}

	@SuppressWarnings("unchecked")
	@Override
	public void saveBrand(Map<String, Object> param) {
		List<T_Vip_Brand_Rate> brandList = (List<T_Vip_Brand_Rate>)param.get("brandList");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT br_mt_code,br_bd_code");
		sql.append(" FROM t_vip_brand_rate t");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.br_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND t.companyid=:companyid");
		List<T_Vip_Brand_Rate> list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Vip_Brand_Rate.class));
		List<T_Vip_Brand_Rate> brandUpdate = new ArrayList<T_Vip_Brand_Rate>();
		if(null != list && list.size() > 0){
			for(T_Vip_Brand_Rate brand:list){
				for(int i = brandList.size()-1;i >= 0;i--){
					if(brandList.get(i).getBr_bd_code().equals(brand.getBr_bd_code()) && brandList.get(i).getBr_mt_code().equals(brand.getBr_mt_code())){
						brandUpdate.add(brandList.get(i));
						brandList.remove(i);
					}
				}
			}
		}
		if(brandList.size() > 0){
			sql.setLength(0);
			sql.append(" INSERT INTO t_vip_brand_rate (");
			sql.append(" br_mt_code,br_bd_code,br_rate,companyid");
			sql.append(" ) VALUES (");
			sql.append(" :br_mt_code,:br_bd_code,:br_rate,:companyid");
			sql.append(" )");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(brandList.toArray()));
		}
		if(brandUpdate.size() > 0){
			sql.setLength(0);
			sql.append("UPDATE t_vip_brand_rate ");
			sql.append(" SET br_rate=:br_rate");
			sql.append(" WHERE br_mt_code=:br_mt_code");
			sql.append(" AND br_bd_code=:br_bd_code");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(brandUpdate.toArray()));
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public void saveType(Map<String, Object> param) {
		List<T_Vip_Type_Rate> typeList = (List<T_Vip_Type_Rate>)param.get("typeList");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT tr_mt_code,tr_tp_code");
		sql.append(" FROM t_vip_type_rate t");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.tr_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" AND m.mt_shop_upcode=:shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND t.companyid=:companyid");
		List<T_Vip_Type_Rate> list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Vip_Type_Rate.class));
		List<T_Vip_Type_Rate> typeUpdate = new ArrayList<T_Vip_Type_Rate>();
		if(null != list && list.size() > 0){
			for(T_Vip_Type_Rate type:list){
				for(int i = typeList.size()-1;i >= 0;i--){
					if(typeList.get(i).getTr_tp_code().equals(type.getTr_tp_code()) && 
							typeList.get(i).getTr_mt_code().equals(type.getTr_mt_code())){
						typeUpdate.add(typeList.get(i));
						typeList.remove(i);
					}
				}
			}
		}
		if(typeList.size() > 0){
			sql.setLength(0);
			sql.append(" INSERT INTO t_vip_type_rate (");
			sql.append(" tr_mt_code,tr_tp_code,tr_rate,companyid");
			sql.append(" ) VALUES (");
			sql.append(" :tr_mt_code,:tr_tp_code,:tr_rate,:companyid");
			sql.append(" )");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(typeList.toArray()));
		}
		if(typeUpdate.size() > 0){
			sql.setLength(0);
			sql.append("UPDATE t_vip_type_rate ");
			sql.append(" SET tr_rate=:tr_rate");
			sql.append(" WHERE tr_mt_code=:tr_mt_code");
			sql.append(" AND tr_tp_code=:tr_tp_code");
			sql.append(" AND companyid=:companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(typeUpdate.toArray()));
		}
	}

	@Override
	public void delType(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM t_vip_type_rate ");
		sql.append(" WHERE tr_id=:tr_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public void delBrand(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM t_vip_brand_rate ");
		sql.append(" WHERE br_id=:br_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public T_Vip_Brand_Rate brandByID(Integer br_id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT br_id,bd_name,mt_name,br_rate ");
		sql.append(" FROM t_vip_brand_rate t");
		sql.append(" JOIN t_base_brand b");
		sql.append(" ON b.bd_code=t.br_bd_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.br_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" WHERE br_id=:br_id");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("br_id", br_id), 
					new BeanPropertyRowMapper<>(T_Vip_Brand_Rate.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Vip_Type_Rate typeByID(Integer tr_id) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tr_id,tp_name,mt_name,tr_rate ");
		sql.append(" FROM t_vip_type_rate t");
		sql.append(" JOIN t_base_type b");
		sql.append(" ON b.tp_code=t.tr_tp_code");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.tr_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" WHERE tr_id=:tr_id");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("tr_id", tr_id), 
					new BeanPropertyRowMapper<>(T_Vip_Type_Rate.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void updateBrand(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE t_vip_brand_rate ");
		sql.append(" SET br_rate=:br_rate");
		sql.append(" WHERE br_id=:br_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public void updateType(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE t_vip_type_rate ");
		sql.append(" SET tr_rate=:tr_rate");
		sql.append(" WHERE tr_id=:tr_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

}
